/*
 * Reference: Web Apps code provided by Prof Charlie Garrod
 * can be used for educational purposes
 * */
package messagepasser;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import document.DocPermissions;

public class PermissionsDAOSQL {
	private List<Connection> connectionPool = new ArrayList<Connection>();

	private String jdbcDriver;
	private String jdbcURL;
	private String tableName;

	public PermissionsDAOSQL(String jdbcDriver, String jdbcURL, String tableName) {
		this.jdbcDriver = jdbcDriver;
		this.jdbcURL    = jdbcURL;
		this.tableName  = tableName;

		createTable();
	}

	private synchronized Connection getConnection() {
		if (connectionPool.size() > 0) {
			return connectionPool.remove(connectionPool.size()-1);
		}

		try {
			Class.forName(jdbcDriver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new AssertionError(e);
		}

		try {
			return DriverManager.getConnection(jdbcURL);
		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}

	private synchronized void releaseConnection(Connection con) {
		connectionPool.add(con);
	}

	private void createTable() {
		Connection con = getConnection();
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(
					"create table "+tableName+
					" (username varchar(20) NOT NULL," +
					" updates varchar(30)," +
					" id MEDIUMINT auto_increment, primary key(id)," +
			" foreign key(username) references aj1_profile(username))");
			stmt.close();
			releaseConnection(con);
		} catch (SQLException e) {
			try { con.close(); } catch (SQLException e2) { /* ignore */ }
		//	System.out.println("Exception creating table: "+e);
		//	System.out.println("...assume it already exists");
		}
	}


	/*	
	public void setupdate(String update, String user){
try {

			Connection con = getConnection();
        	con.setAutoCommit(false);

        	int num = 0;
        	Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("select id from "+tableName+" order by id");
            while (rs.next()) {
            	num = (rs.getInt("id"));
            }
            rs.close();
            stmt.close();

            PreparedStatement pstmt = con.prepareStatement("insert into "+tableName+" values (?,?,?)");
 //           Date d1 = new Date();
            pstmt.setString(1, user);
            pstmt.setString(2, update);
            pstmt.setInt(3, num+1);


            pstmt.executeUpdate();
            pstmt.close();

            con.commit();
            con.setAutoCommit(true);

            releaseConnection(con);

        } catch (SQLException e) {
            throw new AssertionError(e);
        }
	}
	 */
	

	public void insertPerm(int docid, int userid, String accesstype){
		try {
			System.out.println("insertperm");
			System.out.println("Docid = "+docid + " Userid = "+userid+" accesstype = "+accesstype);
			Connection con = getConnection();
			con.setAutoCommit(false);
			int num = 0;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select count(docid) as id from permissions where userid = "+userid+" and docid = "+docid);
			while (rs.next()) {
				num = (rs.getInt("id"));
			}
			rs.close();
			stmt.close();
			PreparedStatement pstmt = null;
			if(num==0){
				pstmt = con.prepareStatement("insert into permissions values (?,?,?)");
				//           Date d1 = new Date();
				pstmt.setInt(1, docid);
				pstmt.setInt(2, userid);
				pstmt.setString(3, accesstype);
				pstmt.executeUpdate();
				pstmt.close();

				con.commit();
				con.setAutoCommit(true);

				releaseConnection(con);
			}
			else{
				stmt = con.createStatement();
				stmt.executeUpdate("update permissions set accesstype = \'"+accesstype+"\' where userid = "+ userid + " and docid = "+docid); 

				stmt.close();
				con.commit();
				con.setAutoCommit(true);
				releaseConnection(con);

				//pstmt = con.prepareStatement("update permissions set accesstype = \'"+accesstype+"\' where userid = "+ userid + " and docid = "+docid);
			}


		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
	public void removePerm(int docid, int userid){
		try {
			Connection con = getConnection();
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.executeUpdate("delete from permissions where userid = "+ userid + " and docid = "+docid); 
			stmt.close();
			con.commit();
			con.setAutoCommit(true);
			releaseConnection(con);
		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}

public void initialize(){
		try {

			Connection con = getConnection();
			con.setAutoCommit(false);
			int num=0;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select count(username) as cnt from "+tableName);
			while (rs.next()) {
				num = (rs.getInt("cnt"));
			}
			rs.close();
			stmt.close();
			if(num == 0)
			{
				PreparedStatement pstmt = con.prepareStatement("insert into "+tableName+" values (?,?,?)");

			}
			con.commit();
			con.setAutoCommit(true);

			releaseConnection(con);

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}



	/*
	public synchronized String[] getDocsList(int userid) {
	   	try {
    	Connection con = getConnection();

        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select id from aj1_updates where username = \'"+user+"\' order by id");

        List<String> list = new ArrayList<String>();
        while (rs.next()) {
        	list.add(rs.getString("id"));
        }
        rs.close();
        stmt.close();
        releaseConnection(con);

        return list.toArray(new String[list.size()]);

    } catch (SQLException e) {
        throw new AssertionError(e);
    }
}*/
	/*
	public String getPwd(String user) {
		try {
			Connection con = getConnection();
			String pd = null;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select password from user where username = \'"+user+"\' ");
			while(rs.next()){
				pd = rs.getString("password");
			}

			rs.close();
			stmt.close();
			releaseConnection(con);

			return pd;

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
	 */

	//Method to get all the permissions of a specific user
	public synchronized  DocPermissions[] getDocPermissions(int ownerid) {
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select docid from permissions where ownerid = "+ownerid+" AND docid NOT IN(Select docid from document where ownerid = \'"+ownerid+"\')");
			//ResultSet rs2 = stmt.executeQuery("select docid from permissions where ownerid = "+ownerid+" AND docid NOT IN(Select docid from document where ownerid = \'"+ownerid+"\')");
			//Getting all doc ids for which user is owner and for which he has entry in permissions table
			
			List<Integer> docids = new ArrayList<Integer>();
			while (rs.next()) {
				docids.add(Integer.parseInt(rs.getString("docid")));
			}
			
			String accesstype = null;
			DocPermissions[] docPermissions = null;

			for(int i=0;i<docids.size();i++){
				rs = stmt.executeQuery("select accesstype from permissions where docid = "+docids.get(i));
				while (rs.next()) {
					accesstype = rs.getString("accesstype");
				}
				docPermissions[i] = new DocPermissions(docids.get(i), ownerid, accesstype);
			}

			rs.close();
			stmt.close();
			releaseConnection(con);

			return docPermissions;

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
	 public synchronized  DocPermissions[] getDocCollaborators(int docid) {
		 try {
		 Connection con = getConnection();

		 Statement stmt = con.createStatement();
		 ResultSet rs = stmt.executeQuery("select userid, accesstype from permissions where docid = "+docid);
		 //ResultSet rs2 = stmt.executeQuery("select docid from permissions where ownerid = "+ownerid+" AND docid NOT IN(Select docid from document where ownerid = \'"+ownerid+"\')");
		 //Getting all doc ids for which user is owner and for which he has entry in permissions table
		 List<Integer> userids = new ArrayList<Integer>();
		 List<String> perms = new ArrayList<String>();
		 while (rs.next()) {
			 userids.add(rs.getInt("userid"));
			 perms.add(rs.getString("accesstype"));
		 }
		 DocPermissions[] docPermissions = new DocPermissions[userids.size()];

		 for(int i=0;i<userids.size();i++){
			 docPermissions[i] = new DocPermissions(docid, userids.get(i), perms.get(i));
		 }

		 rs.close();
		 stmt.close();
		 releaseConnection(con);

		 return docPermissions;

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
	public synchronized  String getPerm(int userid, int docid) {
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select accesstype from permissions where userid = "+userid+" AND docid  = "+docid);
			//ResultSet rs2 = stmt.executeQuery("select docid from permissions where ownerid = "+ownerid+" AND docid NOT IN(Select docid from document where ownerid = \'"+ownerid+"\')");
			//Getting all doc ids for which user is owner and for which he has entry in permissions table

			String accesstyp = null;
			while (rs.next()) {
				accesstyp = rs.getString("accesstype");
			}

			String accesstype = null;
			DocPermissions[] docPermissions = null;


			rs.close();
			stmt.close();
			releaseConnection(con);

			return accesstyp;

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
}
